"""
Orders API routes - Order Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from database.connection import execute_query, execute_transaction

bp = Blueprint('orders', __name__, url_prefix='/api/orders')

@bp.route('', methods=['GET'])
def get_orders():
    """
    Get all orders with optional filtering and pagination
    GET /api/orders?status=Pending&page=1&pageSize=10
    """
    try:
        status = request.args.get('status', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT o.order_id, o.order_date, o.status, o.total_price,
                   r.recipient_id, r.full_name as recipient_name,
                   pc.clinic_name,
                   MIN(d.donor_id) as donor_id,
                   STRING_AGG(DISTINCT d.donor_code, ', ') as donor_code,
                   COUNT(oi.vial_id) as vial_count,
                   EXISTS(
                       SELECT 1 FROM offspring_tracking ot
                       WHERE ot.recipient_id = o.recipient_id
                         AND ot.donor_id IN (
                             SELECT sv2.donor_id FROM order_items oi2
                             JOIN sperm_vials sv2 ON oi2.vial_id = sv2.vial_id
                             WHERE oi2.order_id = o.order_id
                         )
                   ) as has_offspring
            FROM orders o
            JOIN recipients r ON o.recipient_id = r.recipient_id
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            LEFT JOIN order_items oi ON o.order_id = oi.order_id
            LEFT JOIN sperm_vials sv ON oi.vial_id = sv.vial_id
            LEFT JOIN donors d ON sv.donor_id = d.donor_id
        """
        
        params = []
        if status:
            query += " WHERE o.status = %s"
            params.append(status)
        
        query += """
            GROUP BY o.order_id, r.recipient_id, r.full_name, pc.clinic_name
            ORDER BY o.order_id ASC
        """
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        orders = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(DISTINCT o.order_id) as count
            FROM orders o
        """
        count_params = []
        
        if status:
            count_query += " WHERE o.status = %s"
            count_params.append(status)
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': orders,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>', methods=['GET'])
def get_order(order_id):
    """
    Get a single order by ID with items
    GET /api/orders/:id
    """
    try:
        # Get order details
        order_query = """
            SELECT o.order_id, o.recipient_id, o.clinic_id, o.order_date, 
                   o.status, o.total_price, o.staff_id,
                   r.full_name as recipient_name,
                   r.contact_email, pc.clinic_name, pc.address as clinic_address,
                   (SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id) as vial_count
            FROM orders o
            JOIN recipients r ON o.recipient_id = r.recipient_id
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            WHERE o.order_id = %s
        """
        
        order = execute_query(order_query, (order_id,), fetch_one=True)
        
        if not order:
            return jsonify({
                'code': 404,
            'message': 'Order not found'
        }), 404
        
        # Get order items
        items_query = """
            SELECT oi.*, sv.collection_date, sv.motility_percent, sv.sperm_count_mil, sv.status,
                   d.donor_id, d.donor_code, d.blood_type as donor_blood_type
            FROM order_items oi
            JOIN sperm_vials sv ON oi.vial_id = sv.vial_id
            JOIN donors d ON sv.donor_id = d.donor_id
            WHERE oi.order_id = %s
        """
        
        items = execute_query(items_query, (order_id,))
        
        order['items'] = items
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': order
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('', methods=['POST'])
def create_order():
    """
    Create a new order with items (transaction)
    POST /api/orders
    Body: {
        "recipient_id": 1,
        "clinic_id": 1,
        "vial_ids": [1, 2, 3],
        "notes": "xxx"
    }
    """
    try:
        data = request.json
        
        required_fields = ['recipient_id', 'vial_ids']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        if not data['vial_ids'] or len(data['vial_ids']) == 0:
            return jsonify({
                'code': 400,
            'message': 'At least one vial is required'
        }), 400
        
        # Create order
        order_query = """
            INSERT INTO orders (recipient_id, clinic_id, status, total_price)
            VALUES (%s, %s, %s, %s)
            RETURNING order_id
        """
        
        order_params = (
            data['recipient_id'],
            data.get('clinic_id'),
            data.get('status', 'Pending'),
            data.get('total_price', 0)
        )
        
        # Check if all vials are available before creating order
        price_per_unit = data.get('total_price', 0) / len(data['vial_ids']) if len(data['vial_ids']) > 0 else 0
        
        # Verify vials are available and not already in an order
        for vial_id in data['vial_ids']:
            vial_check_query = """
                SELECT sv.status, 
                       EXISTS(
                           SELECT 1 FROM order_items oi
                           JOIN orders o ON oi.order_id = o.order_id
                           WHERE oi.vial_id = %s 
                             AND o.status NOT IN ('Cancelled', 'Completed')
                       ) as in_active_order
                FROM sperm_vials sv
                WHERE sv.vial_id = %s
            """
            vial_check = execute_query(vial_check_query, (vial_id, vial_id), fetch_one=True)
            
            if not vial_check:
                return jsonify({
                    'code': 400,
                    'message': f'Vial {vial_id} not found'
                }), 400
            
            if vial_check['status'] != 'Available':
                return jsonify({
                    'code': 400,
                    'message': f'Vial {vial_id} is not available (status: {vial_check["status"]})'
                }), 400
            
            if vial_check['in_active_order']:
                return jsonify({
                    'code': 400,
                    'message': f'Vial {vial_id} is already in another active order'
                }), 400
        
        # Create order
        order_result = execute_query(order_query, order_params, fetch_one=True)
        
        if not order_result:
            raise Exception("Failed to create order")
        
        order_id = order_result['order_id']
        
        # Create order items (vial status will be updated by trigger)
        queries = []
        for vial_id in data['vial_ids']:
            item_query = """
                INSERT INTO order_items (order_id, vial_id, quantity, price_per_unit)
                VALUES (%s, %s, 1, %s)
            """
            queries.append((item_query, (order_id, vial_id, price_per_unit)))
        
        # Execute order items in transaction
        if queries:
            execute_transaction(queries)
        
        return jsonify({
            'code': 200,
            'message': 'Order created successfully',
            'data': {'order_id': order_id}
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>', methods=['PUT'])
def update_order(order_id):
    """
    Update an order
    PUT /api/orders/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['status']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
            'message': 'No fields to update'
        }), 400
        
        params.append(order_id)
        
        query = f"""
            UPDATE orders
            SET {', '.join(update_fields)}
            WHERE order_id = %s
            RETURNING order_id, status
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Order not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Order updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>/status', methods=['PUT'])
def update_order_status(order_id):
    """
    Update order status (frontend compatible route)
    PUT /api/orders/:id/status
    """
    try:
        data = request.json
        
        if 'status' not in data:
            return jsonify({
                'code': 400,
            'message': 'Status is required'
        }), 400
        
        query = """
            UPDATE orders
            SET status = %s
            WHERE order_id = %s
        """
        
        execute_query(query, (data['status'], order_id), fetch=False)
        
        # Get updated order
        select_query = """
            SELECT order_id, status
            FROM orders
            WHERE order_id = %s
        """
        result = execute_query(select_query, (order_id,), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Order not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Order status updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>', methods=['DELETE'])
def delete_order(order_id):
    """
    Delete an order (set status to Cancelled)
    DELETE /api/orders/:id
    """
    try:
        query = """
            UPDATE orders
            SET status = 'Cancelled'
            WHERE order_id = %s
            RETURNING order_id, status
        """
        
        result = execute_query(query, (order_id,), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Order not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Order deleted successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>/confirm', methods=['POST'])
def confirm_order(order_id):
    """
    Confirm an order (change status to Confirmed and vials to Used)
    POST /api/orders/:id/confirm
    """
    try:
        queries = [
            # Update order status
            ("""
                UPDATE orders
                SET status = 'Paid'
                WHERE order_id = %s
            """, (order_id,)),
            
            # Update vials to Used
            ("""
                UPDATE sperm_vials
                SET status = 'Used'
                WHERE vial_id IN (
                    SELECT vial_id FROM order_items WHERE order_id = %s
                )
            """, (order_id,))
        ]
        
        execute_transaction(queries)
        
        return jsonify({
            'code': 200,
            'message': 'Order confirmed successfully'
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:order_id>/cancel', methods=['POST'])
def cancel_order(order_id):
    """
    Cancel an order (change status to Cancelled and vials back to Available)
    POST /api/orders/:id/cancel
    """
    try:
        queries = [
            # Update order status
            ("""
                UPDATE orders
                SET status = 'Cancelled'
                WHERE order_id = %s
            """, (order_id,)),
            
            # Update vials back to Available
            ("""
                UPDATE sperm_vials
                SET status = 'Available'
                WHERE vial_id IN (
                    SELECT vial_id FROM order_items WHERE order_id = %s
                )
            """, (order_id,))
        ]
        
        execute_transaction(queries)
        
        return jsonify({
            'code': 200,
            'message': 'Order cancelled successfully'
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/stats', methods=['GET'])
def get_order_stats():
    """
    Get order statistics
    GET /api/orders/stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_orders,
                COUNT(*) FILTER (WHERE status = 'Pending') as pending_orders,
                COUNT(*) FILTER (WHERE status = 'Paid') as paid_orders,
                COUNT(*) FILTER (WHERE status = 'Shipped') as shipped_orders,
                COUNT(*) FILTER (WHERE status = 'Completed') as completed_orders,
                COUNT(*) FILTER (WHERE status = 'Cancelled') as cancelled_orders,
                SUM(total_price) FILTER (WHERE status IN ('Paid', 'Shipped', 'Completed')) as total_revenue
            FROM orders
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

